//
//  DataBaseHandler.m
//  UI21_SQLite
//
//  Created by dllo on 16/1/11.
//  Copyright © 2016年 dllo. All rights reserved.
//

#import "DataBaseHandler.h"
#import <sqlite3.h>
#import "ModelForListen.h"

@implementation DataBaseHandler

#pragma mark - 知识点 1 单例

+ (instancetype)shareDataBaseHandler {
    
    //在静态区,只初始化一次 创建数据库对象
    static DataBaseHandler *dataBase = nil;
    
    if (dataBase == nil) {
        dataBase = [[DataBaseHandler alloc] init];
    }
    return dataBase;
}


/** 定义一个 sqlite 结构体 (对象) */

sqlite3 *db;

#pragma mark - 知识点 2 :打开数据库
- (void)open{
    

    // API: sqlite3_open
    if (db != nil) {
        NSLog(@"数据库已经开启");
        return;
    }
     NSString *file = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"model.sqlite"];
    
    NSLog(@"%@",file);
    
    /**
     * @praram filename 数据库文件的路径(注意!类型为 c 字符串)
     *
     * @param ppDb 数据库对象
     *
     **/
  int result = sqlite3_open(file.UTF8String, &db);
    
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    }else{
        NSLog(@"数据打开失败, code = %d", result);
    }
}

#pragma mark - 知识点 3 创建表格

- (void)createTable{

// API: sqlite3_exec
    
// 创建 SQL 语句.
    //////////////
    NSString *createTableSQL = @"CREATE TABLE IF NOT EXISTS lanou1130(number INTEGER PRIMARY KEY AUTOINCREMENT, title text, lastUptrackTitle text, coverSmall text, albumId text, nickname text, coverLarge text, trackTitle text)";
    
  int result = sqlite3_exec(db, createTableSQL.UTF8String, NULL, NULL, nil);

    if (result == SQLITE_OK) {
        NSLog(@"创建表格成功");
    }else{
        NSLog(@"创建表格失败. code :%d", result);
    }
}


#pragma mark - 知识点 4 插入数据

- (void)insertModel:(ModelForListen *)model {
    
    // API :sqlite3_exec
    /////////////
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT into lanou1130(title, lastUptrackTitle, coverSmall, albumId, nickname, coverLarge, trackTitle) VALUES('%@', '%@', '%@', '%@', '%@', '%@', '%@')", model.title, model.lastUptrackTitle, model.coverSmall,model.albumId,model.nickname,model.coverLarge,model.trackTitle];


    int result = sqlite3_exec(db, insertSQL.UTF8String, nil, nil, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"添加数据完成");
    }else{
    
        NSLog(@"添加数据失败.code:%d", result);
    }
}



#pragma mark - 知识点 5 更新数据

- (void)updataModel:(ModelForListen *)model ForNumber:(NSInteger)number{
    
    //API :sqlite2_exec
    
    NSString *updataSQL = [NSString stringWithFormat:@"UPDATE lanou1130 SET title = '%@', lastUptrackTitle = '%@', coverSmall = '%@' albumId = '%@'  nickname = '%@' coverLarge = '%@' trackTitle = '%@' WHERE number = '%lu'",model.title, model.lastUptrackTitle, model.coverSmall, model.albumId,model.nickname,model.coverLarge,model.trackTitle, number];
    int result = sqlite3_exec(db, updataSQL.UTF8String, nil, nil, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"更新成功");
    }else {
    
        NSLog(@"更新失败, code:%d", result);
    }
}


#pragma mark -- 知识点 6 :删除数据

- (void)deleteWithTitle:(NSString *)title {
    
    //API sqlite3_exec

    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM lanou1130 WHERE title = '%@'",title];
    
    int result = sqlite3_exec(db, deleteSQL.UTF8String, nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败 code:%d", result);
    }
}

- (void)deleteWithModel:(ModelForListen *)model {

    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM lanou1130 WHERE title = '%@'",model.title];
    
    int result = sqlite3_exec(db, deleteSQL.UTF8String, nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败 code:%d", result);
    }
}


#pragma mark --知识点 7  查询数据

- (NSArray *)selectForTitle:(NSString *)title{

    /** 存放符合条件的查询数据 (元素是 model 对象)*/
    NSMutableArray *arr = [NSMutableArray array];
    
    // sql 语句
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM lanou1130 WHERE title = '%@'", title];
    
    //API : stmt 结构体, sqlite3_prepareV2,sqlite3_step, sqlite_column
    
    // 声明一个结构体 stmt 对象 (结构体)
    sqlite3_stmt *stmt = nil;
    
    //(准备好的语句对象)
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        
        // 逐行查询 每行都执行查询语句
         while (sqlite3_step(stmt) == SQLITE_ROW) {
            
        // 如果查询条件匹配, 通过 sqlite3_column 函数蔟 取出值;
       
          const unsigned char *title = sqlite3_column_text(stmt, 1);// 取出哪一列的数据值
          const unsigned char *lastUptrackTitle = sqlite3_column_text(stmt, 2);
             const unsigned char *coverSmall = sqlite3_column_text(stmt, 3);
             const unsigned char *albumId = sqlite3_column_text(stmt, 4);
             const unsigned char *nickname = sqlite3_column_text(stmt, 5);
             const unsigned char *coverLarge = sqlite3_column_text(stmt, 6);
             const unsigned char *trackTitle = sqlite3_column_text(stmt, 7);
//          int age = sqlite3_column_int(stmt, 3);
            
            
            // 创建 model 对象, 赋值之后放入数组中
            
            ModelForListen *model = [[ModelForListen alloc] init];
             
            model.title = [NSString stringWithUTF8String:(const char *) title];
            model.lastUptrackTitle = [NSString stringWithUTF8String:(const char*) lastUptrackTitle];
            model.coverSmall = [NSString stringWithUTF8String:(const char*) coverSmall];
            model.albumId = [NSString stringWithUTF8String:(const char*)albumId];
            model.nickname = [NSString stringWithUTF8String:(const char*)nickname];
             model.coverLarge = [NSString stringWithUTF8String:(const char*)coverLarge];
             model.trackTitle = [NSString stringWithUTF8String:(const char*)trackTitle];
            
            [arr addObject:model];
        }
        NSLog(@"准备就绪,查询中...");
        // 销毁对象
        sqlite3_finalize(stmt);
        
    }else {
        NSLog(@"不能正常查询, code:%d", result);
         // 销毁对象
        sqlite3_finalize(stmt);
    }
    return arr;
}


- (NSArray *)selectForModel {

    
    /** 存放符合条件的查询数据 (元素是 model 对象)*/
    NSMutableArray *arr = [NSMutableArray array];
    
    // sql 语句
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM lanou1130"];
    
    //API : stmt 结构体, sqlite3_prepareV2,sqlite3_step, sqlite_column
    
    // 声明一个结构体 stmt 对象 (结构体)
    sqlite3_stmt *stmt = nil;
    
    //(准备好的语句对象) -1 代表查询全部对象.
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        
        // 逐行查询 每行都执行查询语句
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            // 如果查询条件匹配, 通过 sqlite3_column 函数蔟 取出值;
            
            const unsigned char *title = sqlite3_column_text(stmt, 1);// 取出哪一列的数据值
            const unsigned char *lastUptrackTitle = sqlite3_column_text(stmt, 2);
            const unsigned char *coverSmall = sqlite3_column_text(stmt, 3);
            const unsigned char *albumId = sqlite3_column_text(stmt, 4);
            const unsigned char *nickname = sqlite3_column_text(stmt, 5);
            const unsigned char *coverLarge = sqlite3_column_text(stmt, 6);
            const unsigned char *trackTitle = sqlite3_column_text(stmt, 7);
            //          int age = sqlite3_column_int(stmt, 3);
            
            
            // 创建 model 对象, 赋值之后放入数组中
            
            ModelForListen *model = [[ModelForListen alloc] init];
            
            model.title = [NSString stringWithUTF8String:(const char *) title];
            model.lastUptrackTitle = [NSString stringWithUTF8String:(const char*) lastUptrackTitle];
            model.coverSmall = [NSString stringWithUTF8String:(const char*) coverSmall];
            model.albumId = [NSString stringWithUTF8String:(const char*)albumId];
            model.nickname = [NSString stringWithUTF8String:(const char*)nickname];
            model.coverLarge = [NSString stringWithUTF8String:(const char*)coverLarge];
            model.trackTitle = [NSString stringWithUTF8String:(const char*)trackTitle];
            
            [arr addObject:model];
        }
        NSLog(@"准备就绪,查询中...");
        // 销毁对象
        sqlite3_finalize(stmt);
        
    }else {
        NSLog(@"不能正常查询, code:%d", result);
        // 销毁对象
        sqlite3_finalize(stmt);
    }
    return arr;




}

#pragma mark --知识点 8 删除表单

-(void)dropTable{

    NSString *dropSQL = @"DROP TABLE lanou1130";
    
    int result = sqlite3_exec(db, dropSQL.UTF8String, nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除表格成功!!!!");
    }else{
        NSLog(@"删除失败");
    }
}


#pragma mark --关掉数据库

- (void)closeDB {

    // API:sqlite3_close
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库关闭成功");
        db = nil;
    }else{
    NSLog(@"数据库关闭失败. code :%d", result);
    }
}








@end
